Statement caching is a technique used in many systems to improve performance by caching “prepared statement handles” that are used repeatedly, such as in a loop or in a method that is called repeatedly. Prepared statements are expensive to create from scratch. Creation of a prepared statement involves allocating the client side data structure for the prepared statement handle, and sending the statement text and binding variable metadata to the server. The server parses and semantically analyzes the statement, followed by which the server sends the statement metadata to the client, which is processed by the client and saved away. All of these steps consume CPU on the client and server and also results in additional network traffic, both in the request sent to the server and response received from the server.
For a given SQL statement that is repeatedly executed by the client, it is therefore desirable to avoid repeating all of these steps unnecessarily by keeping the prepared statement handle cached on the client, thereby avoiding the need to recreate it from scratch on every execution. Therefore, statement caching is an important optimization that helps to reduce client and server CPU usage and also helps reduce network traffic.
The size of the prepared statement cache for a user's session determines how many prepared statement handles may be cached for the user session. Thus, it is important for the size of a session cache to be the right size. Too small a cache can result in contention between statements vying to occupy space in the cache, thereby resulting in thrashing in the cache, which can result in performance issues, negating the benefits of statement caching.
However, if the cache is too large, then the cache may occupy more memory on the client than needed, and holding on to more resources on the server as well (as the server needs to keep some state around for each prepared statement cached on the client). Overall memory usage on the client and server will be greater, resulting in less memory available for other client and server processes. Other software on the client and server may not be able to obtain needed memory, potentially causing degradation of overall system performance, both on the client and the server.
A static cache size is often insufficient because the demands of each session may change over time. For example, certain sessions may process more statements, thus requiring more memory, during certain times of the day, or certain times of the week. Thus, there is a need to dynamically increase or decrease the statement cache size based on the amount of activity a session is experiencing.
One option is to have an application developer programmatically tune the cache. A system may periodically return metrics, such as client CPU usage, server CPU usage, and network statistics, to the application developer, who may then determine how the session caches will be resized. However, this process may be error-prone and time consuming, and often incurs a great deal of programming overhead, especially for systems with a large number of sessions.
Thus, there is a need for a client side statement cache system that can automatically allocate or restrict cache memory as needed dynamically in response to changing workloads. This may be based on some specific overall constraints, for example, on how much memory can be used for the purposes of caching prepared statements on the client.